import connector.DatabaseDriver
from connector.DatabaseDriver import DatabaseDriver
def get_all_books(driver: DatabaseDriver) -> tuple:  
    results: tuple = driver.query_all("select * from book")  
    return results  
def user_search_books(driver: DatabaseDriver, key: str) -> tuple:  
    results: tuple = driver.query_all(  
        "select * from book where book_name like '%{}%' or publisher like '%{}%' or  author like '%{}%'".format(key, key , key))
    return results  
def get_book_by_name_author_type(driver: DatabaseDriver, book_name: str = "", author:  
str = "", book_type: tuple = None) -> tuple:
    if book_name == "" and author == "" and book_type == "":  
        return get_all_books()  
    else:  
        if len(book_type) > 0:  
            book_type = book_type[0]  
    sql: str = ""
    if book_name != "" and author != "" and book_type is not None:
        sql: str = "select * from book where book_name like '%{}%' and author like '%{}%' and cate_id = {}".format(book_name,author,book_type)
    elif book_name != "" and author != "":
        sql: str = "select * from book where book_name like '%{}%' and author like '%{}%'".format(book_name, author)
    elif book_name != "" and book_type is not None:
        sql: str = "select * from book where book_name like '%{}%' and cate_id={}".format(book_name, book_type)
    elif author != "" and book_type is not None:
        sql: str = "select * from book where author like '%{}%' and cate_id={}".format(author, book_type)
        sql: str = "select * from book where book_name like '%{} % '".format(book_name)
        sql: str = "select * from book where cate_id={}".format(book_type)
    elif author != "":
        sql: str = "select * from book where author like '%{}%'".format(author)
        return driver.query_all(sql=sql, log=True)


def user_search_books_by_book_id(driver: DatabaseDriver, book_id: int) -> tuple:
    results: tuple = driver.query_all('select * from book where book_id={}'.format(book_id))
    return results
def get_borrow_books_by_user_id(driver: DatabaseDriver, user_id: int) -> list:
    return driver.query_all("select borrow.*,book.book_name,book.author,book.save_loc from book,borrow where book.book_id = borrow.book_id and borrow.user_id={} and return_time is NULL".format(user_id))
def user_search_books_by_book_name(driver: DatabaseDriver, book_name: str):
    return driver.query_all('select * from book where book_name = "{}"'.format(book_name))
def update_book(driver: DatabaseDriver, book_id:int, book_name:str,book_publisher:str, book_author:str, cate_id:int, nums:int, save_loc:str)->int:
    return driver.update('update book set book_name="{}",publisher="{}",author="{}",cate_id={},nums={},save_loc="{}" where book_id={}'.format(book_name,book_publisher,book_author,cate_id,nums,save_loc,book_id))
def insert_book(driver: DatabaseDriver, book_name:str, book_publisher:str,book_author:str, cate_id:int, nums:int, save_loc:str)->int:
    return driver.insert('insert into book(book_name,publisher,author,cate_id,nums,save_loc) values ("{}","{}","{}",{},{},"{}")'.format(book_name,book_publisher,book_author,cate_id,nums,save_loc))